package com.dhcc.DRGsHosp.application.impl;

import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.CaseAnalysisApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

@Named
@Transactional
public class CaseAnalysisApplicationImpl implements CaseAnalysisApplication {
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;
	
  //出院病例统计表 - 一个层次的总体情况
	
  	@Override
  	@Cacheable(value = "drgscom3.1.1", key = "'loadTotalHspOutCaseInfo' + #mr.dateFrom + #mr.dateTo + #mr.deps + #mr.hospCode + #mr.verRegion ")
  	public List<Map<String,Object>> loadTotalHspOutCaseInfo(IndexCal mr) {
  		StringBuilder sql = new StringBuilder();
  		String dateFrom = mr.getDateFrom();//开始时间
  		String dateTo = mr.getDateTo();//结束时间
  		String deps = mr.getDeps();
  		String verRegion = mr.getVerRegion();//分组方案
  		String hospCode = mr.getHospCode();
  		
  		//入组率 = 入组病例数/(全部病例数-大于60天病例数)
  		//未入组病例数为t_patientdrg中notin_reason字段不为0的病例数
  		sql.append(" --出院病例数	入组病例数	未入组病例数	大于60天病例数	入组率(%)	死亡病例数	死亡率(%) \n ");
  		sql.append(" select count(*) totalCaseCnt, \n ");
  		sql.append(" nvl(sum(case when pd.pardrg_code is not null and mr.P00320000 < 61 then  1 else 0 end),0) as InCaseCnt, \n ");
  		sql.append(" nvl(sum(case when pd.pardrg_code is null and mr.P00320000 < 61   then  1 else 0 end),0) as notInCaseCnt, \n ");
  		sql.append(" nvl(sum(case when mr.P00320000 >= 61 then  1 else 0 end),0) as over60Cnt, \n ");
  		sql.append(" nvl(round(sum(case when pd.pardrg_code is not null and mr.P00320000 < 61 then  1 else 0 end)/sum(case when mr.P00320000 < 61 then  1 else 0 end)*100,2),0) InRatio, \n ");
  		sql.append(" nvl(sum(case when mr.P00620001 = '5' then  1 else 0 end),0) deadCnt, \n ");
  		sql.append(" nvl(round(sum(case when mr.P00620001 = '5' then  1 else 0 end)/count(*)*100,2),0) deadRatio \n ");
  		sql.append(" from drgs.mr_baseinfo bs left join drgs.mr_ipinfo mr on bs.pmainindex=mr.pmainindex \n ");
  		sql.append(" left join drgs.t_patientdrg pd on mr.pmainindex = pd.parid and pd.ver_region='"+verRegion+"' \n ");
  		sql.append(" left join drgs.t_department dep \n ");
  		sql.append(" on mr.P00300001 = dep.dep_code \n ");
  		sql.append(" left join drgs.t_depgroup dg \n ");
  		sql.append(" on dg.depgroup_code = dep.pardepgroup_code \n ");
  		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n ");
  		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
  		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  	  		sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
  		List<Map<String,Object>> totalList=jdbcTemplateObj.queryForList(sql.toString());
  		return totalList;
  	}
  	//各死亡风险组入组病例统计表 - 一个层次的总体情况
  	@Override
  	@Cacheable(value = "drgscom3.1.1", key = "'loadTotalHspRiskGroupInfo' + #mr.dateFrom + #mr.dateTo+ #mr.stdYear + #mr.deps + #mr.hospCode + #mr.verRegion ")
  	public List<Map<String,Object>> loadTotalHspRiskGroupInfo(IndexCal mr) {
  		StringBuilder sql = new StringBuilder();
  		String dateFrom = mr.getDateFrom();//开始时间
  		String dateTo = mr.getDateTo();//结束时间
  		String deps = mr.getDeps();
  		String depCodes = "";
  		if(StringUtils.hasLength(deps)){
  			depCodes = "'"+deps.replace(",", "','")+"'";
  		}
  		String stdId = mr.getStdId();
  		String verRegion = mr.getVerRegion();
  		String hospCode = mr.getHospCode();
  		
  		sql.append(" --各风险组 人数 DRGs组数  死亡数 死亡率 \n ");
  		sql.append(" select decode(a.as_risk_level,0,'零风险组',1,'低风险组',2,'中低风险组',3,'中高风险组',4,'高风险组','新增病组') asRiskLevel, \n ");
  		sql.append(" a.RiskCaseCnt as riskCaseCnt,a.drgCnt as drgCnt,nvl(b.RiskDeadCaseCnt,0)  as riskDeadCaseCnt,nvl(b.RiskDeadCaseCnt/a.RiskCaseCnt,0)*100  as riskDeadRatio, \n ");
  		sql.append(" a.as_risk_level  asRiskLevelCode  \n ");
  		sql.append(" from(  \n ");
  		sql.append(" select std.as_risk_level as_risk_level,count(distinct mr.pmainindex) RiskCaseCnt,count(distinct drg.drg_code) drgCnt  \n ");
  		sql.append(" from DRGs.MR_BASEINFO bs    \n ");
  		sql.append(" LEFT JOIN DRGS.MR_IPINFO MR ON BS.PMAININDEX = MR.PMAININDEX  \n ");
  		sql.append(" left join DRGs.t_patientdrg pd  \n ");
  		sql.append(" on mr.pmainindex=pd.parid and pd.ver_region='"+verRegion+"'  \n ");
  		sql.append(" left join DRGs.t_Drgs drg  \n ");
  		sql.append(" on pd.pardrg_code=drg.drg_code  \n ");
  		sql.append(" left join DRGs.t_department dep on mr.P00300001 = dep.dep_code  \n ");
  		sql.append(" left join DRGs.t_annualstd std   \n ");
  		sql.append(" on pd.pardrg_code=std.as_drg_code and std.as_id='"+stdId+"'  \n ");
  		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n ");
  		sql.append("  and pd.pardrg_code is not null and mr.P00320000 < 61  \n ");
  		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
  		if(StringUtils.hasLength(deps)){
  			sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
  		sql.append(" group by std.as_risk_level) a  \n ");//求各风险组的病例数和病组数
  		sql.append(" left join (select std.as_risk_level as_risk_level,count(distinct mr.pmainindex) RiskDeadCaseCnt \n ");
  		sql.append(" from DRGs.MR_BASEINFO bs    \n ");
  		sql.append(" LEFT JOIN DRGS.MR_IPINFO MR ON BS.PMAININDEX = MR.PMAININDEX  \n ");
  		sql.append("  left join DRGs.t_patientdrg pd  \n ");
  		sql.append(" on mr.pmainindex=pd.parid and pd.ver_region='"+verRegion+"' \n ");
  		sql.append(" left join DRGs.t_department dep on mr.P00300001 = dep.dep_code  \n ");
  		sql.append(" left join DRGs.t_annualstd std \n ");
  		sql.append(" on pd.pardrg_code=std.as_drg_code and std.as_id='"+stdId+"' \n ");
  		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n ");
  		sql.append(" and pd.pardrg_code is not null and mr.P00320000 < 61  \n ");
  		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
  		sql.append(" and mr.P00620001='5'  \n ");
  		if(StringUtils.hasLength(deps)){
  			sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
  		sql.append(" group by std.as_risk_level) b \n ");
  		sql.append(" on a.as_risk_level=b.as_risk_level \n ");//求各风险组的死亡病例数
  		sql.append(" order by a.as_risk_level \n ");
  		List<Map<String,Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
  		return totalList;
  	}

	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'loadNotInReason' + #mr.dateFrom + #mr.dateTo + #mr.deps + #mr.hospCode + #mr.verRegion")
	public List<Map<String,Object>> loadNotInReason(IndexCal mr) {
		StringBuilder sql = new StringBuilder();
		String dateFrom = mr.getDateFrom();//开始日期
		String dateTo = mr.getDateTo();//结束日期
		String deps = mr.getDeps();
		String verRegion = mr.getVerRegion();
		String hospCode = mr.getHospCode();
		//更改未入组病例数的选取规则，只从T_PATIENTDRG里判断，不把未参加分组的病例数归纳到未入组里面
		sql.append("---未入组原因 --饼图数据 \n "); 
		sql.append(" select gl.notin_reason as code,count(*) as value from drgs.T_PATIENTDRG gl  \n ");
		sql.append(" left join drgs.MR_IPINFO mr on gl.parid = mr.pmainindex  and gl.ver_region='"+verRegion+"' \n ");
		sql.append(" left join drgs.mr_baseinfo bs on mr.pmainindex=bs.pmainindex  \n ");
		sql.append(" left join DRGs.t_department dep on mr.P00300001 = dep.dep_code  \n ");
		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" and gl.pardrg_code is null and mr.P00320000 < 61  \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  	  		sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
		sql.append(" group by gl.notin_reason   \n ");
		sql.append(" order by notin_reason asc \n ");
		List<Map<String,Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
		return totalList;
	}
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'loadNotInDep' + #mr.dateFrom + #mr.dateTo + #mr.deps + #mr.hospCode + #mr.verRegion")
	public List<Map<String,Object>> loadNotInDep(IndexCal mr) {
		StringBuilder sql = new StringBuilder();
		String dateFrom = mr.getDateFrom();//开始日期
		String dateTo = mr.getDateTo();//结束日期
		String deps = mr.getDeps();
		String verRegion = mr.getVerRegion();//分组方案
		String hospCode = mr.getHospCode();
		
		sql.append(" --未入组科室分布--饼图数据  \n ");
		sql.append("select  d.dep_name as depName,mr.P00300001 as depCode,count(mr.pmainindex) cnt  \n");
		sql.append("from drgs.MR_IPINFO mr left join drgs.t_department d on mr.P00300001=d.dep_code  \n");
		sql.append("left join drgs.mr_baseinfo bs on mr.pmainindex=bs.pmainindex  \n ");
		sql.append("left join drgs.T_PATIENTDRG g on mr.pmainindex=g.parid and g.ver_region='"+verRegion+"' where g.pardrg_code is null and mr.P00320000 < 61  \n ");
		sql.append("and to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  	  		sql.append(" and d.dep_code in( "+ depCodes +") \n ");
  		}
		sql.append("group by mr.P00300001,d.dep_name order by count(mr.pmainindex)desc  \n");
		List<Map<String,Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
		return totalList;
	}
	
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'loadNotInDiagnosis' + #mr.dateFrom + #mr.dateTo + #mr.deps+ #mr.hospCode + #mr.verRegion")
	public List<Map<String,Object>> loadNotInDiagnosis(IndexCal mr) {
		StringBuilder sql = new StringBuilder();
		String dateFrom = mr.getDateFrom();//开始日期
		String dateTo = mr.getDateTo();//结束日期
		String deps = mr.getDeps();
		String verRegion = mr.getVerRegion();//分组方案
		String hospCode = mr.getHospCode();
		
		sql.append(" --未入组主要诊断分布--饼图数据  \n ");
		sql.append(" select i1.icd10_diag_code icd10Code,i1.icd10_diag_code || ' ' || i1.icd10_diag_desc icd10Desc, cnt from ( \n ");
		sql.append(" select i2.parent_id, count(1) cnt from  \n ");
		sql.append(" (select t.parid from drgs.t_patientdrg t left join drgs.mr_ipinfo mr on mr.pmainindex=t.parid where t.pardrg_code is null and t.ver_region='"+verRegion+"' and mr.P00320000 < 61 ) p join \n ");
		sql.append(" (select t.PMAININDEX, P00300001,substr(diag.PD0101, 0, 3) icdcategory from drgs.MR_IPINFO t   \n ");
		sql.append("  left join drgs.mr_baseinfo bs on t.pmainindex=bs.pmainindex    \n ");
		sql.append("  LEFT JOIN drgs.MR_DIAGNOSE diag ON t.PMAININDEX=diag.PMAININDEX AND diag.PDTYPE='M' \n ");
		sql.append(" where to_char(t.P00290000, 'yyyy-mm-dd') between '"+dateFrom+"' and  '"+dateTo+"'  \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("  ) m on p.parid = m.PMAININDEX \n ");
		sql.append(" join (select i.icd10_diag_code, i.parent_id from drgs.t_icd10_diagnosis_layer i \n ");
		sql.append(" where i.icd10_diag_level = 3) i3 on m.icdcategory = i3.icd10_diag_code join  \n ");
		sql.append(" (select i.id, i.parent_id from drgs.t_icd10_diagnosis_layer i where i.icd10_diag_level = 2) i2 \n ");
		sql.append(" on i3.parent_id = i2.id  \n ");
		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  			sql.append(" left join drgs.t_department dep on m.P00300001=dep.dep_code \n ");
  	  		sql.append(" where dep.dep_code in( "+ depCodes +") \n ");
  		}
		sql.append(" group by i2.parent_id) c join \n ");
		sql.append(" (select i.id, i.icd10_diag_code, i.icd10_diag_desc, i.parent_id from drgs.t_icd10_diagnosis_layer i where i.icd10_diag_level = 1) i1 \n ");
		sql.append(" on c.parent_id = i1.id order by cnt desc \n ");
		List<Map<String,Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
		return totalList;
	}

	/**
	 *  @Description：科室未入组原因病例明细
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.CaseAnalysisApplication#notInDepCaseData(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'notInDepCaseData' + #mr.dateFrom + #mr.dateTo+ #mr.pardepCode+ #mr.stdYear + #mr.deps+ #mr.hospCode + #mr.verRegion")
	public List<Map<String, Object>> notInDepCaseData(IndexCal mr) {
		StringBuilder sql = new StringBuilder();
		String depCode = mr.getPardepCode();//科室代码
		String dateFrom = mr.getDateFrom();//开始日期
		String dateTo = mr.getDateTo();//结束日期
		String deps = mr.getDeps();
		String verRegion = mr.getVerRegion();//分组方案
		String hospCode = mr.getHospCode();
		
	    sql.append(" select mr.pmainindex as id,BS.P00040000 IP_COUNT,BS.P00050000 mrNo,bs.P00060000 as patientName,BS.P00070001 as sex,BS.P00090000 as ageYear,to_char(mr.P00250000,'yyyy-mm-dd') as inDate,to_char(mr.P00290000,'yyyy-mm-dd') as outDate,  \n ");
		sql.append(" mr.P00300000 as outDep,mr.P00320000 as inDays,mr.P00800000 as costamount,costsum.DRUG_COST drugcost,diag.PD0101 as diagnoseCode,diag.PD0100 as diagnoseDessmain,diaginfo.oth_diaginfo_code ,diaginfo.oth_diaginfo_desc , \n");
		sql.append(" operinfo.operinfo_code ,operinfo.operinfo_desc,gl.notin_reason, resdia.DIACODE res_dia_code,resdia.DIADESC res_dia_desc,resoper.OPERCODE RES_OPER_CODE,resoper.OPERDESC RES_OPER_DESC,resmaindia.BJ_ICD_CODE,resmaindia.BJ_ICD_DESC\n");
		
		sql.append(" from drgs.T_PATIENTDRG gl left join drgs.MR_IPINFO mr on gl.parid = mr.pmainindex and gl.ver_region='"+verRegion+"' \n ");
		sql.append(" LEFT JOIN drgs.mr_baseinfo BS ON BS.PMAININDEX=MR.PMAININDEX  \n ");
		sql.append(" left join drgs.mr_expense_sum costsum on mr.pmainindex=costsum.pmainindex  \n ");
		sql.append(" LEFT JOIN drgs.MR_DIAGNOSE diag ON mr.PMAININDEX=diag.PMAININDEX AND diag.PDTYPE='M' \n ");

		sql.append(" LEFT JOIN (SELECT diag.pmainindex,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_CODE ELSE diag.Pd0101 END BJ_ICD_CODE,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_DESC ELSE diag.Pd0100 END BJ_ICD_DESC \n ");
		sql.append(" FROM DRGS.MR_DIAGNOSE diag \n ");
		sql.append(" LEFT JOIN DRGS.MR_IPINFO IP  ON diag.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n ");
		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_DIA TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REDIA ON REDIA.HOSP_ICD_CODE=diag.PD0101 and REDIA.hosp_icd_desc=diag.Pd0100 \n ");
		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD10_VERSION=REDIA.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN   '"+dateFrom+"' and '"+dateTo+"' AND instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" ) resmaindia on MR.PMAININDEX = resmaindia.PMAININDEX \n ");
		
		sql.append(" LEFT JOIN (SELECT DIAG.PMAININDEX, LISTAGG(DIAG.PD0100,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_desc,  \n ");
		sql.append(" LISTAGG(DIAG.PD0101,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_code   \n ");		
		sql.append(" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"' AND DIAG.PDTYPE = 'O' \n ");
		sql.append(" GROUP BY DIAG.PMAININDEX) DIAGINFO ON MR.PMAININDEX = DIAGINFO.PMAININDEX  \n ");
		
		sql.append(" LEFT JOIN (\n");
		sql.append(" SELECT REDIA.PMAININDEX, LISTAGG(REDIA.DIA_CODE,'|')  WITHIN GROUP (ORDER BY REDIA.PDINDEX)  DIACODE,  \n");
		sql.append(" LISTAGG(REDIA.DIA_DESC,'|')  WITHIN GROUP (ORDER BY REDIA.PDINDEX)  DIADESC   FROM( \n");
		sql.append(" SELECT DIAG.PMAININDEX,DIAG.PDINDEX ,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_CODE ELSE DIAG.PD0101 END DIA_CODE,\n");
		sql.append(" CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_DESC ELSE DIAG.PD0100 END DIA_DESC \n");
		sql.append(" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n");

		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_DIA TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REDIA ON REDIA.HOSP_ICD_CODE=DIAG.PD0101 and REDIA.hosp_icd_desc=diag.Pd0100 \n");

		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD10_VERSION=REDIA.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"'  AND DIAG.PDTYPE = 'O' \n");
		sql.append(" ) REDIA GROUP BY REDIA.PMAININDEX\n");
		sql.append(" ) resdia on MR.PMAININDEX = resdia.PMAININDEX \n");

		sql.append(" LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG(OPER.PO0101,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_code, \n ");
		sql.append(" LISTAGG(OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_desc \n ");
		sql.append(" FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" GROUP BY OPER.PMAININDEX) OPERINFO ON MR.PMAININDEX = OPERINFO.PMAININDEX  \n ");
		
		sql.append(" LEFT JOIN (\n");
		sql.append(" SELECT REDOPER.PMAININDEX, LISTAGG(REDOPER.OPER_CODE,'|')  WITHIN GROUP (ORDER BY REDOPER.POINDEX)  OPERCODE, \n");
		sql.append(" LISTAGG(REDOPER.OPER_DESC,'|')  WITHIN GROUP (ORDER BY REDOPER.POINDEX)  OPERDESC	FROM( \n");
		sql.append(" SELECT OPER.PMAININDEX ,OPER.POINDEX,CASE WHEN REOPER.BJ_ICD_CODE IS NOT NULL THEN REOPER.BJ_ICD_CODE ELSE OPER.PO0101 END OPER_CODE,\n");
		sql.append(" CASE WHEN REOPER.BJ_ICD_CODE IS NOT NULL THEN REOPER.BJ_ICD_DESC ELSE OPER.PO0100 END OPER_DESC\n");
		sql.append(" FROM DRGS.Mr_Operation oper LEFT JOIN DRGS.MR_IPINFO IP  ON oper.PMAININDEX = IP.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n");

		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_OPER TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REOPER ON REOPER.HOSP_ICD_CODE=OPER.PO0101 AND REOPER.HOSP_ICD_DESC=OPER.PO0100 \n");

		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD9_VERSION=REOPER.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" )REDOPER GROUP BY REDOPER.PMAININDEX \n");
		sql.append(" ) resoper on MR.PMAININDEX = resoper.PMAININDEX \n");
		
		sql.append(" left join drgs.t_department dep on dep.dep_code =mr.P00300001   \n ");
		sql.append(" left join drgs.t_depgroup dg on dg.depgroup_code = dep.pardepgroup_code  \n ");
		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" AND gl.pardrg_code is null and mr.P00320000 < 61\n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0  \n ");
		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  	  		sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
		if(StringUtils.hasLength(depCode)){
			if(depCode.contains("&")){
				depCode=depCode.replace("&", ",");
				sql.append("  and mr.P00300001 not in("+depCode+")   \n ");
			}
			else{
				sql.append("  and mr.P00300001 = '"+depCode+"'   \n ");	
			}
		}

		List<Map<String, Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
		return totalList;
	}
	/**
	 * 
	 *  @Description：未入组原因病例明细
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.CaseAnalysisApplication#notInReasonCaseData(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'notInReasonCaseData' + #mr.dateFrom + #mr.dateTo+ #mr.type1 + #mr.deps + #mr.hospCode + #mr.verRegion")
	public List<Map<String, Object>> notInReasonCaseData(IndexCal mr) {
		StringBuilder sql = new StringBuilder();
		String flag = mr.getType1();//未入组原因代码
		String dateFrom = mr.getDateFrom();//开始日期
		String dateTo = mr.getDateTo();//结束日期
		String deps = mr.getDeps();
  		String verRegion = mr.getVerRegion();//分组方案
		String hospCode = mr.getHospCode();
  		
	    sql.append(" select mr.pmainindex as id,BS.P00040000 IP_COUNT,BS.P00050000 mrNo,bs.P00060000 as patientName,BS.P00070001 as sex,BS.P00090000 as ageYear,to_char(mr.P00250000,'yyyy-mm-dd') as inDate,to_char(mr.P00290000,'yyyy-mm-dd') as outDate,  \n ");
		sql.append(" mr.P00300000 as outDep,mr.P00320000 as inDays,mr.P00800000 as costamount,costsum.DRUG_COST drugcost,diag.PD0101 as diagnoseCode,diag.PD0100 as diagnoseDessmain,diaginfo.oth_diaginfo_code ,diaginfo.oth_diaginfo_desc , \n");
		sql.append(" operinfo.operinfo_code ,operinfo.operinfo_desc,gl.notin_reason, resdia.DIACODE res_dia_code,resdia.DIADESC res_dia_desc,resoper.OPERCODE RES_OPER_CODE,resoper.OPERDESC RES_OPER_DESC,resmaindia.BJ_ICD_CODE,resmaindia.BJ_ICD_DESC\n");
		
		sql.append(" from drgs.T_PATIENTDRG gl left join drgs.MR_IPINFO mr on gl.parid = mr.pmainindex and gl.ver_region='"+verRegion+"' \n ");
		sql.append(" LEFT JOIN drgs.mr_baseinfo BS ON BS.PMAININDEX=MR.PMAININDEX  \n ");
		sql.append(" left join drgs.mr_expense_sum costsum on mr.pmainindex=costsum.pmainindex  \n ");
		sql.append(" LEFT JOIN drgs.MR_DIAGNOSE diag ON mr.PMAININDEX=diag.PMAININDEX AND diag.PDTYPE='M' \n ");
		
		sql.append(" LEFT JOIN (SELECT diag.pmainindex,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_CODE ELSE diag.Pd0101 END BJ_ICD_CODE,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_DESC ELSE diag.Pd0100 END BJ_ICD_DESC \n ");
		sql.append(" FROM DRGS.MR_DIAGNOSE diag \n ");
		sql.append(" LEFT JOIN DRGS.MR_IPINFO IP  ON diag.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n ");
		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_DIA TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REDIA ON REDIA.HOSP_ICD_CODE=diag.PD0101 and REDIA.hosp_icd_desc=diag.Pd0100 \n ");
		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD10_VERSION=REDIA.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN   '"+dateFrom+"' and '"+dateTo+"' AND instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" ) resmaindia on MR.PMAININDEX = resmaindia.PMAININDEX \n ");
		
		sql.append(" LEFT JOIN (SELECT DIAG.PMAININDEX, LISTAGG(DIAG.PD0100,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_desc,  \n ");
		sql.append(" LISTAGG(DIAG.PD0101,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_code   \n ");		
		sql.append(" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"' AND DIAG.PDTYPE = 'O' \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" GROUP BY DIAG.PMAININDEX) DIAGINFO ON MR.PMAININDEX = DIAGINFO.PMAININDEX  \n ");
		
		
		sql.append(" LEFT JOIN (\n");
		sql.append(" SELECT REDIA.PMAININDEX, LISTAGG(REDIA.DIA_CODE,'|')  WITHIN GROUP (ORDER BY REDIA.PDINDEX)  DIACODE,  \n");
		sql.append(" LISTAGG(REDIA.DIA_DESC,'|')  WITHIN GROUP (ORDER BY REDIA.PDINDEX)  DIADESC   FROM( \n");
		sql.append(" SELECT DIAG.PMAININDEX,DIAG.PDINDEX  ,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_CODE ELSE DIAG.PD0101 END DIA_CODE,\n");
		sql.append(" CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_DESC ELSE DIAG.PD0100 END DIA_DESC \n");
		sql.append("FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX  \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n");

		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_DIA TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REDIA ON REDIA.HOSP_ICD_CODE=DIAG.PD0101 and REDIA.hosp_icd_desc=diag.Pd0100 \n");

		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD10_VERSION=REDIA.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"'  AND DIAG.PDTYPE = 'O'  \n");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" ) REDIA GROUP BY REDIA.PMAININDEX\n");
		sql.append(" ) resdia on MR.PMAININDEX = resdia.PMAININDEX \n");

		sql.append(" LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG(OPER.PO0101,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_code, \n ");
		sql.append(" LISTAGG(OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_desc \n ");
		sql.append(" FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" GROUP BY OPER.PMAININDEX) OPERINFO ON MR.PMAININDEX = OPERINFO.PMAININDEX  \n ");
		
		sql.append(" LEFT JOIN (\n");
		sql.append("SELECT REDOPER.PMAININDEX, LISTAGG(REDOPER.OPER_CODE,'|')  WITHIN GROUP (ORDER BY REDOPER.POINDEX)  OPERCODE, \n");
		sql.append(" LISTAGG(REDOPER.OPER_DESC,'|')  WITHIN GROUP (ORDER BY REDOPER.POINDEX)  OPERDESC	FROM( \n");
		sql.append(" SELECT OPER.PMAININDEX ,OPER.POINDEX,CASE WHEN REOPER.BJ_ICD_CODE IS NOT NULL THEN REOPER.BJ_ICD_CODE ELSE OPER.PO0101 END OPER_CODE,\n");
		sql.append(" CASE WHEN REOPER.BJ_ICD_CODE IS NOT NULL THEN REOPER.BJ_ICD_DESC ELSE OPER.PO0100 END OPER_DESC\n");
		sql.append(" FROM DRGS.Mr_Operation oper LEFT JOIN DRGS.MR_IPINFO IP  ON oper.PMAININDEX = IP.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n");

		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_OPER TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REOPER ON REOPER.HOSP_ICD_CODE=OPER.PO0101 AND REOPER.HOSP_ICD_DESC=OPER.PO0100 \n");

		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD9_VERSION=REOPER.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n");
		sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN   '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" )REDOPER GROUP BY REDOPER.PMAININDEX \n");
		sql.append(" ) resoper on MR.PMAININDEX = resoper.PMAININDEX \n");
		
		sql.append(" left join drgs.t_department dep on dep.dep_code =mr.P00300001   \n ");
		sql.append(" left join drgs.t_depgroup dg on dg.depgroup_code = dep.pardepgroup_code  \n ");
		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0  \n ");
		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  	  		sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
		if(StringUtils.hasLength(flag)){
			sql.append(" and gl.notin_reason = '"+flag+"' and mr.P00320000 < 61 \n ");
		}
		else{
			sql.append(" and gl.pardrg_code is null and mr.P00320000 < 61 \n ");
		}
		List<Map<String, Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
		return totalList;
	}
	/**
	 * 
	 *  @Description：诊断未入组原因病例明细查询
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.CaseAnalysisApplication#notInDiagCaseData(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'notInDiagCaseData' + #mr.dateFrom + #mr.dateTo+ #mr.type1 + #mr.deps + #mr.hospCode + #mr.verRegion")
	public List<Map<String, Object>> notInDiagCaseData(IndexCal mr) {
		StringBuilder sql = new StringBuilder();
		String diagnosis = mr.getType1();//诊断章节
		String dateFrom = mr.getDateFrom();//开始日期
		String dateTo = mr.getDateTo();//结束日期
		String deps = mr.getDeps();
  		String verRegion = mr.getVerRegion();//分组方案
  		String hospCode = mr.getHospCode();
  		
	    sql.append(" select mr.pmainindex as id,BS.P00040000 IP_COUNT,BS.P00050000 mrNo,bs.P00060000 as patientName,BS.P00070001 as sex,BS.P00090000 as ageYear,to_char(mr.P00250000,'yyyy-mm-dd') as inDate,to_char(mr.P00290000,'yyyy-mm-dd') as outDate,  \n ");
		sql.append("mr.P00300000 as outDep,mr.P00320000 as inDays,mr.P00800000 as costamount,costsum.DRUG_COST drugcost,diag.PD0101 as diagnoseCode,diag.PD0100 as diagnoseDessmain,diaginfo.oth_diaginfo_code ,diaginfo.oth_diaginfo_desc , \n");
		sql.append(" operinfo.operinfo_code ,operinfo.operinfo_desc,pd.notin_reason, resdia.DIACODE res_dia_code,resdia.DIADESC res_dia_desc,resoper.OPERCODE RES_OPER_CODE,resoper.OPERDESC RES_OPER_DESC,resmaindia.BJ_ICD_CODE,resmaindia.BJ_ICD_DESC\n");
		sql.append(" from drgs.t_patientdrg pd left join drgs.MR_IPINFO mr on pd.parid = mr.pmainindex and pd.ver_region='"+verRegion+"' \n ");
		sql.append(" LEFT JOIN drgs.mr_baseinfo BS ON BS.PMAININDEX=MR.PMAININDEX  \n ");
		sql.append(" left join drgs.t_department dep on mr.p00300001=dep.dep_code  \n ");
		sql.append(" left join drgs.mr_expense_sum costsum on mr.pmainindex=costsum.pmainindex  \n ");
		sql.append(" LEFT JOIN drgs.MR_DIAGNOSE diag ON mr.PMAININDEX=diag.PMAININDEX AND diag.PDTYPE='M' \n ");
		
		sql.append(" LEFT JOIN (SELECT diag.pmainindex,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_CODE ELSE diag.Pd0101 END BJ_ICD_CODE,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_DESC ELSE diag.Pd0100 END BJ_ICD_DESC \n ");
		sql.append(" FROM DRGS.MR_DIAGNOSE diag \n ");
		sql.append(" LEFT JOIN DRGS.MR_IPINFO IP  ON diag.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n ");
		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_DIA TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REDIA ON REDIA.HOSP_ICD_CODE=diag.PD0101 and REDIA.hosp_icd_desc=diag.Pd0100 \n ");
		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD10_VERSION=REDIA.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN   '"+dateFrom+"' and '"+dateTo+"' AND instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" ) resmaindia on MR.PMAININDEX = resmaindia.PMAININDEX \n ");
		
		sql.append(" LEFT JOIN (SELECT DIAG.PMAININDEX, LISTAGG(DIAG.PD0100,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_desc,  \n ");
		sql.append(" LISTAGG(DIAG.PD0101,'|')  WITHIN GROUP (ORDER BY DIAG.PDINDEX)  oth_diaginfo_code   \n ");		
		sql.append(" FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"' AND DIAG.PDTYPE = 'O' \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" GROUP BY DIAG.PMAININDEX) DIAGINFO ON MR.PMAININDEX = DIAGINFO.PMAININDEX  \n ");
		
		sql.append(" LEFT JOIN (\n");
		sql.append(" SELECT REDIA.PMAININDEX, LISTAGG(REDIA.DIA_CODE,'|')  WITHIN GROUP (ORDER BY REDIA.PDINDEX)  DIACODE,  \n");
		sql.append(" LISTAGG(REDIA.DIA_DESC,'|')  WITHIN GROUP (ORDER BY REDIA.PDINDEX)  DIADESC   FROM( \n");
		sql.append(" SELECT DIAG.PMAININDEX,DIAG.PDINDEX  ,CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_CODE ELSE DIAG.PD0101 END DIA_CODE,\n");
		sql.append(" CASE WHEN REDIA.BJ_ICD_CODE IS NOT NULL THEN REDIA.BJ_ICD_DESC ELSE DIAG.PD0100 END DIA_DESC \n");
		sql.append("FROM DRGS.MR_DIAGNOSE DIAG LEFT JOIN DRGS.MR_IPINFO IP  ON DIAG.PMAININDEX = IP.PMAININDEX  \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n");

		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_DIA TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REDIA ON REDIA.HOSP_ICD_CODE=DIAG.PD0101 and REDIA.hosp_icd_desc=diag.Pd0100 \n");

		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD10_VERSION=REDIA.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"'  AND DIAG.PDTYPE = 'O' \n");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" ) REDIA GROUP BY REDIA.PMAININDEX\n");
		sql.append(" ) resdia on MR.PMAININDEX = resdia.PMAININDEX \n");

		sql.append(" LEFT JOIN (SELECT OPER.PMAININDEX, LISTAGG(OPER.PO0101,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_code, \n ");
		sql.append(" LISTAGG(OPER.PO0100,'|')  WITHIN GROUP (ORDER BY OPER.POINDEX)  operinfo_desc \n ");
		sql.append(" FROM DRGS.MR_OPERATION OPER LEFT JOIN DRGS.MR_IPINFO IP  ON OPER.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append(" WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN  '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" GROUP BY OPER.PMAININDEX) OPERINFO ON MR.PMAININDEX = OPERINFO.PMAININDEX  \n ");
		
		sql.append(" LEFT JOIN (\n");
		sql.append("SELECT REDOPER.PMAININDEX, LISTAGG(REDOPER.OPER_CODE,'|')  WITHIN GROUP (ORDER BY REDOPER.POINDEX)  OPERCODE, \n");
		sql.append(" LISTAGG(REDOPER.OPER_DESC,'|')  WITHIN GROUP (ORDER BY REDOPER.POINDEX)  OPERDESC	FROM( \n");
		sql.append(" SELECT OPER.PMAININDEX,OPER.POINDEX ,CASE WHEN REOPER.BJ_ICD_CODE IS NOT NULL THEN REOPER.BJ_ICD_CODE ELSE OPER.PO0101 END OPER_CODE,\n");
		sql.append(" CASE WHEN REOPER.BJ_ICD_CODE IS NOT NULL THEN REOPER.BJ_ICD_DESC ELSE OPER.PO0100 END OPER_DESC\n");
		sql.append(" FROM DRGS.Mr_Operation oper LEFT JOIN DRGS.MR_IPINFO IP  ON oper.PMAININDEX = IP.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG GL ON gl.parid = IP.pmainindex and gl.ver_region='"+verRegion+"' \n");

		sql.append(" LEFT JOIN (SELECT TIRD.BJ_ICD_CODE,TIRD.BJ_ICD_DESC, TIRD.HOSP_ICD_CODE,TIRD.HOSP_ICD_DESC,TIRD.DRG_ICD_VERSION \n ");
		sql.append(" FROM DRGS.T_ICDMATCH_RESULT_OPER TIRD \n ");
		sql.append(" WHERE TIRD.ICDMAT_TYPE = '1' \n ");
		sql.append(" ) REOPER ON REOPER.HOSP_ICD_CODE=OPER.PO0101 AND REOPER.HOSP_ICD_DESC=OPER.PO0100 \n");

		sql.append(" JOIN DRGS.T_DRGVERSION DRGVERSION ON DRGVERSION.ICD9_VERSION=REOPER.drg_icd_version AND DRGVERSION.VER_CODE=GL.VER_CODE \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = IP.PMAININDEX \n ");
		sql.append("  WHERE TO_CHAR(IP.P00290000,'YYYY-MM-DD') BETWEEN   '"+dateFrom+"' and '"+dateTo+"' \n");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" )REDOPER GROUP BY REDOPER.PMAININDEX \n");
		sql.append(" ) resoper on MR.PMAININDEX = resoper.PMAININDEX \n");
		
		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" and pd.pardrg_code is null and mr.P00320000 < 61 \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  	  		sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
		sql.append(" and diag.PD0101 in (  \n ");
		sql.append(" select diagnosis from drgs.T_ICD10_DIAGNOSIS_LAYER t,  \n ");
		sql.append(" (select distinct substr(diag.PD0101,0,3) diag_code,diag.PD0101 diagnosis \n ");
		sql.append(" from drgs.t_patientdrg pd \n ");
		sql.append(" left join drgs.MR_IPINFO mr on mr.pmainindex=pd.parid  and pd.ver_region='"+verRegion+"' \n ");
		sql.append(" left join drgs.MR_DIAGNOSE diag on mr.pmainindex=diag.pmainindex  \n ");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = MR.PMAININDEX \n ");
		sql.append(" where to_char(mr.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append(" and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append(" and diag.PDTYPE='M' \n ");
		sql.append(" and pd.pardrg_code is null and mr.P00320000 < 61\n ");
		sql.append(" group by diag.PD0101) b \n ");
		sql.append(" where b.diag_code \n ");
		sql.append(" between substr(t.icd10_diag_code,0,instr(t.icd10_diag_code,'-',1)-1) and substr(t.icd10_diag_code,instr(t.icd10_diag_code,'-',1)+1) \n ");

		if(diagnosis.contains("&")){
			diagnosis=diagnosis.replace("&", ",");
			sql.append(" and t.icd10_diag_level=1 and t.icd10_diag_code not in("+diagnosis+")) \n ");
		}else{
			sql.append(" and t.icd10_diag_level=1 and t.icd10_diag_code='"+diagnosis+"') \n ");		
		}
		List<Map<String, Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
		return totalList;
	}
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'drgsDetail' + #mr.dateFrom + #mr.dateTo+ #mr.stdYear+ #mr.type1 + #mr.deps + #mr.hospCode + #mr.verRegion")
	public List<Map<String, Object>> drgsDetail(IndexCal mr) {
		StringBuilder sql = new StringBuilder();
		String riskType = mr.getType1();//风险等级类型
		String dateFrom = mr.getDateFrom();//开始日期
		String dateTo = mr.getDateTo();//结束日期
		String stdId = mr.getStdId();
		String deps = mr.getDeps();
  		String verRegion = mr.getVerRegion();//分组方案
  		String hospCode = mr.getHospCode();
  		
		//查询风险等级的对应的病组
		sql.append("  select pd.pardrg_code||'-'||drg.drg_desc drgName,decode(std.as_risk_level,0,'零风险组',1,'低风险组',2,'中低风险组',3,'中高风险组',4,'高风险组') riskLevel, \n ");
		sql.append("  std.as_weight as weight,round(avg(a.P00800000),2) avgfee,round(avg(a.P00320000),2) avgday  \n ");
		sql.append("  from DRGs.MR_IPINFO a  \n ");
		sql.append("  LEFT JOIN DRGS.MR_BASEINFO BS  ON BS.PMAININDEX = a.PMAININDEX \n ");
		sql.append("  left join DRGs.t_patientdrg pd  \n ");
		sql.append("  on a.PMAININDEX=pd.parid and pd.ver_region='"+verRegion+"'  \n ");
		sql.append("  left join drgs.t_drgs drg  \n ");
		sql.append("  on drg.DRG_CODE=pd.PARDRG_CODE and pd.VER_CODE=drg.VER_CODE \n ");
		sql.append("  left join drgs.t_department dep on a.p00300001=dep.dep_code  \n ");
		sql.append("  left join DRGs.t_annualstd std  \n ");
		sql.append("  on pd.pardrg_code=std.as_drg_code and std.as_id= '"+stdId+"' \n ");
		sql.append("  where to_char(a.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
		sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
		sql.append("  and pd.pardrg_code is not null and a.P00320000 < 61   \n ");
		if(StringUtils.hasLength(deps)){
  			String depCodes = "'"+deps.replace(",", "','")+"'";
  	  		sql.append(" and dep.dep_code in( "+ depCodes +") \n ");
  		}
		if("5".equals(riskType)){
			sql.append("  and std.as_risk_level is null  \n ");
		}else{
			sql.append("  and std.as_risk_level="+riskType+"  \n ");
		}
		sql.append("  group by pd.pardrg_code,drg.drg_desc,std.as_risk_level,std.as_weight  \n ");
		List<Map<String, Object>> totalList = jdbcTemplateObj.queryForList(sql.toString());
		return totalList;
	}
}
